package com.java2007.hotel.dao.impl;

import com.java2007.hotel.dao.DinnerTableDao;
import com.java2007.hotel.pojo.DinnerTable;
import com.java2007.hotel.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * @author Reckeys
 * @version V1.0
 * @date 2021/01/26
 **/
public class DinnerTableDaoImpl implements DinnerTableDao {

    /**
     * 通过餐桌状态查询对应状态的餐桌
     * @param tableStatus
     * @return
     */
    @Override
    public List<DinnerTable> findDinnerTableByStatus(int tableStatus) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT table_id tableId,table_name tableName,table_status tableStatus,table_scheduled_time tableScheduledTime,table_create_time tableCreateTime,table_update_time tableUpdateTime,table_create_user_id tableCreateUserId FROM t_dinner_table WHERE table_status = ?";
        return queryRunner.query(sql,new BeanListHandler<>(DinnerTable.class),tableStatus);
    }

    /**
     * 按名字查询餐桌，如果名字为null则查询所有
     * @param tableName
     * @return
     */
    @Override
    public List<DinnerTable> findDinnerTableByName(String tableName) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT " +
                "table_id tableId,table_name tableName,table_status tableStatus,table_scheduled_time tableScheduledTime,table_create_time tableCreateTime,table_update_time tableUpdateTime,table_create_user_id tableCreateUserId FROM t_dinner_table " +
                "WHERE table_name LIKE ?";
        return queryRunner.query(sql,new BeanListHandler<>(DinnerTable.class),tableName);
    }

    /**
     * 通过餐桌id查询餐桌信息
     * @param dinnerTableId
     * @return
     */
    @Override
    public DinnerTable findDinnerTableByDinnerTableId(Integer dinnerTableId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT " +
                "table_id tableId,table_name tableName,table_status tableStatus,table_scheduled_time tableScheduledTime,table_create_time tableCreateTime,table_update_time tableUpdateTime,table_create_user_id tableCreateUserId " +
                "FROM t_dinner_table " +
                "WHERE table_id = ?";
        return queryRunner.query(sql,new BeanHandler<>(DinnerTable.class),dinnerTableId);
    }

    /**
     * 通过餐桌id更改预定时间和预定状态
     * @param dinnerTable
     * @return
     */
    @Override
    public int updateDinnerTableStatus(DinnerTable dinnerTable) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "UPDATE " +
                "t_dinner_table " +
                "SET table_id = ?,table_name = ?,table_status = ?,table_scheduled_time = ?,table_create_time = ?,table_update_time = ?,table_create_user_id  = ? " +
                "WHERE table_id = ?";
        return queryRunner.update(JdbcUtils.getConnection(),sql,dinnerTable.getTableId(),dinnerTable.getTableName(),dinnerTable.getTableStatus(),dinnerTable.getTableScheduledTime(),dinnerTable.getTableCreateTime(),dinnerTable.getTableUpdateTime(),dinnerTable.getTableCreateUserId(),dinnerTable.getTableId());
    }

    /**
     * 通过tableId删除餐桌
     * @param tableId
     */
    @Override
    public int deleteDinnerTableByTableId(Integer tableId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "DELETE FROM t_dinner_table WHERE table_id = ?";
        return queryRunner.update(JdbcUtils.getConnection(),sql,tableId);
    }

    /**
     * 添加餐桌
     * @param dinnerTable
     * @return
     */
    @Override
    public int addDinnerTable(DinnerTable dinnerTable) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "INSERT INTO t_dinner_table " +
                "(table_name,table_status,table_create_time,table_update_time,table_create_user_id)" +
                "VALUES(?,?,?,?,?)";
        return queryRunner.update(JdbcUtils.getConnection(),sql,dinnerTable.getTableName(),dinnerTable.getTableStatus(),dinnerTable.getTableCreateTime(),dinnerTable.getTableUpdateTime(),dinnerTable.getTableCreateUserId());
    }

    /**
     * 通过餐桌id查询餐桌
     * @param tableId
     */
    @Override
    public DinnerTable findDinnerTableByTableId(Integer tableId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT " +
                "table_id tableId,table_name tableName,table_status tableStatus,table_scheduled_time tableScheduledTime,table_create_time tableCreateTime,table_update_time tableUpdateTime,table_create_user_id tableCreateUserId FROM t_dinner_table " +
                "WHERE table_id = ?";
        return queryRunner.query(sql,new BeanHandler<>(DinnerTable.class),tableId);
    }

}
